import threading
import sqlite3
import os
import sys


class seeting:
    SQLITE_PATH = os.path.join(os.path.dirname(os.path.abspath(sys.argv[0])),"sqlite")
    IMG_PATH = os.path.join(os.path.dirname(os.path.abspath(sys.argv[0])),"img")
    SQLITE_FILE_PATH = os.path.join(os.path.dirname(os.path.abspath(sys.argv[0])),"sqlite","data.db")


class sqlitedb(seeting):
    def __init__(self):
        self.lock = threading.Lock()
        creat_dir_file(self.IMG_PATH)
        if not os.path.exists(self.SQLITE_FILE_PATH):
            creat_dir_file(self.SQLITE_PATH)
            self.SQLITE_DB, self.CUR_DB = new_sqlite(self.SQLITE_FILE_PATH)
            # 初始化建表
            self.CUR_DB.execute('''CREATE TABLE mylog(
                                ID INTEGER PRIMARY KEY    AUTOINCREMENT,
                                time_year      INT(4)          NOT NULL,
                                time_month     INT(2)          NOT NULL,
                                time_day       INT(2)          NOT NULL,
                                time_datetime  VARCHAR(10)     NOT NULL,
                                mymoney        VARCHAR(10)     NOT NULL,
                                content        VARCHAR(255)    NOT NULL);''')

            self.CUR_DB.execute('''CREATE TABLE library(
                                myid INTEGER(50) PRIMARY KEY   NOT NULL,
                                sum            INT(10)         );''')

            self.CUR_DB.execute('''CREATE TABLE inventory(
                                ID INTEGER PRIMARY KEY    AUTOINCREMENT,
                                myid           INT(50)         NOT NULL,
                                name           VARCHAR(255)    NOT NULL,
                                price          VARCHAR(10)     NOT NULL,
                                ations         VARCHAR(255),
                                path           VARCHAR(255),
                                FOREIGN KEY (myid) REFERENCES library(myid));''')

            self.SQLITE_DB.commit()
        else:
            self.SQLITE_DB, self.CUR_DB = new_sqlite(self.SQLITE_FILE_PATH)

    def insert_data(self,name,field,data):
        # INSERT INTO mylog (ID,time,content) VALUES (null,'2020','测试')
        sql = "INSERT INTO {} ({}) VALUES ({})".format(name,field,data)
        ret = self.sql_run(sql)
        if ret:
            return ret.rowcount

    def update_data(self,name,field,conditions):
        # UPDATE The_log set content = '更改' where time = '2021'
        sql = "UPDATE {} SET {} WHERE {}".format(name,field,conditions)
        ret = self.sql_run(sql)
        if ret:
            return ret.rowcount

    def select_data(self,field,name):
        # SELECT time,content from The_log
        sql = "SELECT {} FROM {}".format(field,name)
        ret = self.sql_run(sql)
        if ret:
            return ret.fetchall()

    def delete_data(self,name,conditions):
        # DELETE from The_log where time='2020'
        sql = "DELETE FROM {} WHERE {}".format(name,conditions)
        ret = self.sql_run(sql)
        if ret:
            return ret.rowcount

    def sql_run(self,sql):
        try:
            self.lock.acquire(True)
            ret = self.CUR_DB.execute(sql)
            self.SQLITE_DB.commit()
        except Exception as e:
            print("sqlerr:",e,sql)
            # erre_log(e)
            ret = None
            self.SQLITE_DB.rollback()
        finally:
            self.lock.release()
        return ret

    def close_db(self):
        self.SQLITE_DB.close()


def new_sqlite(pathdb):
    SQLITE_DB = sqlite3.connect(pathdb, check_same_thread=False)
    CUR_DB = SQLITE_DB.cursor()
    return SQLITE_DB,CUR_DB


def creat_dir_file(mypath):
    if not os.path.exists(mypath):
        os.mkdir(mypath)


mysql = sqlitedb()

